# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html
from scrapy.utils.project import get_project_settings
import pymysql


class MysqlPipeline(object):
    def open_spider(self, spider):
        settings = get_project_settings()
        host = settings['HOST']
        port = settings['PORT']
        user = settings['USER']
        password = settings['PASSWORD']
        charset = settings['CHARSET']
        name = settings['NAME']
        self.conn = pymysql.connect(host=host, port=port, user=user,
                                    password=password, db=name, charset=charset)

    def close_spider(self, spider):
        self.conn.close()

    def process_item(self, item, spider):
        sql = 'insert into tb_scenic(name, intro, addr, time_cost) values("%s", "%s", "%s", "%s")' % (item['name'],
                                     item['intro'], item['addr'], item['time_cost'])
        print(sql)
        self.cursor = self.conn.cursor()
        try:
            self.cursor.execute(sql)
            self.conn.commit()
        except:
            self.conn.rollback()
        return item
